package cn.ainit.butn.proceduce;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.springframework.stereotype.Repository;

import cn.ainit.common.ReturnObject;
import cn.ainit.common.cwReturnObject;
import cn.ainit.sys.common.dao.SysBaseDao;


@Repository
public class ButnProDaoImpl extends SysBaseDao implements ButnProDao{
	
	@Override
	public ReturnObject execPro(ButScripParams buParams) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		CallableStatement callStmt = null;
		Connection conn = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
			List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql)
			.setString(0, buParams.getDsid())
			.setString(1, buParams.getCode())
			.list();
			if (commonP != null && !commonP.isEmpty()) {
				Object callObj = commonP.get(0).getProcedure();
				String callName = "";
				if (callObj != null || "".equals(callObj.toString())) {
					callName = "{call "+callObj.toString()+"(?,?,?,?)}";
					//执行存储过程(?,?,?,?)   pkid   uid staid restr
					String tmpPk = "";
					Object[] tmpPkArr = {};
					Map tmpMap = new HashMap();
					for(int i = 0; i < buParams.getDataRows().size(); i++){
						tmpMap = buParams.getDataRows().get(i);
						tmpPkArr = tmpMap.keySet().toArray();
						tmpPk = tmpMap.get(tmpPkArr[0].toString()).toString();
						callStmt = conn.prepareCall(callName);
						callStmt.setString(1, tmpPk);
						callStmt.setString(2, buParams.getUid());
						callStmt.setString(3, buParams.getStaid());
						callStmt.registerOutParameter(4, OracleTypes.VARCHAR);
						//						returnObject.setInfo("按钮的存储过程["+callName+"]未找到或执行失败<br/>必须包含参数" +
						//								[pkid in,uid in,staid in,msg out]");
						callStmt.executeUpdate();
						returnObject.setInfo(callStmt.getString(4));
						returnObject.setCount(i+1);
					}
				}
			}else{
				throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid()+"]<br/>[code:"+buParams.getCode()+"]");
			}
		} catch (Exception e) {
			
			e.printStackTrace();
			returnObject.setMsg("error");
			returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
		}finally{
			if(callStmt != null){
				callStmt.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return returnObject;
	}
	
	
	
	
	@Override
	public ReturnObject execButnPro(ButScripParams buParams) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String msgs ="";
		try {
			Connection conn = null;
			CallableStatement callStmt = null;
			try {
				conn = this.getJdbcTemplate().getDataSource().getConnection();
				String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
				List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql)
				.setString(0, buParams.getDsid())
				.setString(1, buParams.getCode())
				.list(); 						//获取存储过程名称
				if (commonP != null && !commonP.isEmpty()) {
					Object callObj = commonP.get(0).getProcedure();
					String callName = "";
					if (callObj != null || "".equals(callObj.toString())) {
						callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
						System.out.println("存储过程："+callName+"---单据Id："+buParams.getDjId()+"---操作人Id："+buParams.getUid()+"站点Id："+buParams.getStaid()+"第四个输出参数");
						//执行存储过程(?,?,?,?)   pkid   uid staid restr

						callStmt = conn.prepareCall(callName);
						callStmt.setString(1, buParams.getDjId());
						callStmt.setString(2, buParams.getDsid());
						callStmt.setString(3, buParams.getUid());
						callStmt.setString(4, buParams.getStaid());
						callStmt.registerOutParameter(5, OracleTypes.VARCHAR);
						callStmt.executeUpdate();
						returnObject.setMsg("success");
						msgs=callStmt.getString(5);
						if(msgs!=null){
							int index=msgs.indexOf("@");
							if(index!=-1){
								msgs=msgs.substring(0,index);
							}
						}
					}
				}else{
					throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid()+"]<br/>[code:"+buParams.getCode()+"]");
				}
			}catch (SQLException se) {
				String sbmsgs= se.getMessage();
				String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
				if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
					String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
					//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
					String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
					callStmt = conn.prepareCall(errSql);  
					callStmt.registerOutParameter(1, Types.VARCHAR);  
					callStmt.setString(2,exproc);
					callStmt.setString(3,buParams.getDsid());
					callStmt.execute();
					msgs= callStmt.getString(1);
				}else if(se.getErrorCode()== 20001){
					msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
				}else{
					msgs=sbmsgs;
				}
				if(msgs!=null){
					int index=msgs.indexOf("@");
					if(index!=-1){
						msgs=msgs.substring(0,index);
					}
				}
				returnObject.setMsg("error");
			}finally{
				if (callStmt!= null) {
					callStmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			msgs+=e.getMessage();
			returnObject.setMsg("error");
		}
		returnObject.setInfo(msgs);
		return returnObject;
	}
	
	
	

	@Override
	public ReturnObject butproSel(ButScripParams buParams)throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String msgs ="";
		try {
			Connection conn = null;
			CallableStatement callStmt = null;
			try {
				conn = this.getJdbcTemplate().getDataSource().getConnection();
				String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
				List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql)
				.setString(0, buParams.getDsid())
				.setString(1, buParams.getCode())
				.list(); 						//获取存储过程名称
				if (commonP != null && !commonP.isEmpty()) {
					Object callObj = commonP.get(0).getProcedure();
					String callName = "";
					if (callObj != null || "".equals(callObj.toString())) {
						callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
						System.out.println("存储过程："+callName+"---单据Id："+buParams.getDjId()+"---操作人Id："+buParams.getUid()+"站点Id："+buParams.getStaid()+"第四个输出参数");
						//执行存储过程(?,?,?,?)   pkid   uid staid restr

						callStmt = conn.prepareCall(callName);
						callStmt.setString(1, buParams.getDjId());
						callStmt.setString(2, buParams.getDsid());
						callStmt.setString(3, buParams.getUid());
						callStmt.setString(4, buParams.getStaid());
						callStmt.registerOutParameter(5, OracleTypes.VARCHAR);
						callStmt.executeUpdate();
						returnObject.setMsg("success");
						msgs=callStmt.getString(5);
					}
				}else{
					throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid()+"]<br/>[code:"+buParams.getCode()+"]");
				}
			} catch (SQLException se) {
				String sbmsgs= se.getMessage();
				String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
				if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
					String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
					//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
					String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
					callStmt = conn.prepareCall(errSql);  
					callStmt.registerOutParameter(1, Types.VARCHAR);  
					callStmt.setString(2,exproc);
					callStmt.setString(3,buParams.getDsid());
					callStmt.execute();
					msgs= callStmt.getString(1);
				}else if(se.getErrorCode()== 20001){
					msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
				}else{
					msgs=sbmsgs;
				}
				returnObject.setMsg("error");
			}finally{
				if (callStmt!= null) {
					callStmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			msgs+=e.getMessage();
			returnObject.setMsg("error");
		}
		returnObject.setInfo(msgs);
		return returnObject;
	}

	
	
	
	
	/***
	 * 批量审核
	 */
	@Override
	public ReturnObject execButnProPLV(ButScripParams buParams) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String retMsgs ="";
		String [] djIds=buParams.getDjId().split(";");
		CallableStatement callStmt = null;
		Connection conn = null;
		try {
			String dsId=buParams.getDsid().split(";").length>0?buParams.getDsid().split(";")[0]:buParams.getDsid();
			String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
			List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql).setString(0,dsId).setString(1,buParams.getPrefix()+"_VERIFY_BUT").list();
			if (commonP != null && !commonP.isEmpty()) {
				for (int i = 0; i < djIds.length; i++) {
					String msgs="";
					try {
						conn = this.getJdbcTemplate().getDataSource().getConnection();
						Object callObj = commonP.get(0).getProcedure();
						String callName = "";
						if (callObj != null || "".equals(callObj.toString())) {
							callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
							//执行存储过程(?,?,?,?)   pkid dsId  uid staid restr
							callStmt = conn.prepareCall(callName);
							callStmt.setString(1,djIds[i]);
							callStmt.setString(2, buParams.getDsid());
							callStmt.setString(3, buParams.getUid());
							callStmt.setString(4, buParams.getStaid());
							callStmt.registerOutParameter(5,OracleTypes.VARCHAR);
							callStmt.executeUpdate();
							returnObject.setMsg("success");
							msgs=callStmt.getString(5);
							if(msgs==null||msgs.indexOf("@")==-1){
								System.out.println("单据DsId为："+dsId+"的审核的存储过程的V_OUT的输出不正确，没有传递djCode");
							}else{
								int index=msgs.indexOf("@");
								String tempMsg="";
								if(index!=-1){
									if(index==0){
										tempMsg="审核成功";
										retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
									}else{
										tempMsg=msgs.substring(0,index);
										retMsgs+="单据【"+msgs.substring(index+1)+"】 成功提示："+tempMsg+"<br/>";
									}

								}
							}
						}
					}catch (SQLException se) {
						String sbmsgs= se.getMessage();
						String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
						if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
							String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
							//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
							String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
							callStmt = conn.prepareCall(errSql);  
							callStmt.registerOutParameter(1, Types.VARCHAR);  
							callStmt.setString(2,exproc);
							callStmt.setString(3,buParams.getDsid());
							callStmt.execute();
							msgs= callStmt.getString(1);
						}else if(se.getErrorCode()== 20001){
							msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
							int index=msgs.indexOf("@");
							String tempMsg="";
							if(index!=-1){
								if(index==0){
									tempMsg="审核失败";
									retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
								}else{
									tempMsg=msgs.substring(0,index);
									retMsgs+="单据【"+msgs.substring(index+1)+"】 <span style='color:red'>失败提示： </span>"+tempMsg+"<br/>";
								}
							}else {
								tempMsg="审核失败";
								retMsgs+="单据【"+djIds[i]+"】 "+msgs.substring(index+1)+"<br/>";
							}
						}else{
                            retMsgs=semsgs;
							break;
						}
						returnObject.setMsg("error");
					}catch (Exception e) {
						e.printStackTrace();
						returnObject.setMsg("error");
						retMsgs=e.getMessage();
						//returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
					}finally{
						if(callStmt != null){
							callStmt.close();
						}
						if(conn != null){
							conn.close();
						}
					}

				}
			}else{
				throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid().split(";")[0]+"]的审核的存储过程");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (returnObject.getMsg().equals("error")) {
			if (retMsgs==null||"".equals(retMsgs)) {

			}
		}
		retMsgs="<div style='line-height:23px;'>"+retMsgs+"</div>";
		returnObject.setInfo(retMsgs);
		return returnObject;
	}
	
	/***
	 * 财务：报销单和综合费用单的(通用的批量审核的执行)
	 */
	@Override
	public cwReturnObject cwPlVerifyButn(ButScripParams buParams) throws Exception {
		cwReturnObject cwReturnObject = new cwReturnObject();
		String retMsgs ="";
		String [] djIds=buParams.getDjId().split(";");
		CallableStatement callStmt = null;
		Connection conn = null;
		
		String dsId=buParams.getDsid().split(";").length>0?buParams.getDsid().split(";")[0]:buParams.getDsid();
		String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
		List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql).setString(0,dsId).setString(1,buParams.getPrefix()+"_VERIFY_BUT").list();
		if (commonP != null && !commonP.isEmpty()) {
			for (int i = 0; i < djIds.length; i++) {
				String msgs="";
				try {
					conn = this.getJdbcTemplate().getDataSource().getConnection();
					Object callObj = commonP.get(0).getProcedure();
					String callName = "";
					if (callObj != null || "".equals(callObj.toString())) {
						callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
						//执行存储过程(?,?,?,?)   pkid dsId  uid staid restr
						callStmt = conn.prepareCall(callName);
						callStmt.setString(1,djIds[i]);
						callStmt.setString(2, buParams.getDsid());
						callStmt.setString(3, buParams.getUid());
						callStmt.setString(4, buParams.getStaid());
						callStmt.registerOutParameter(5,OracleTypes.VARCHAR);
						callStmt.executeUpdate();
						cwReturnObject.setMsg("success");
						msgs=callStmt.getString(5);
						if(msgs==null||msgs.indexOf("@")==-1){
							System.out.println("单据DsId为："+dsId+"的审核的存储过程的V_OUT的输出不正确，没有传递djCode");
						}else{
							int index=msgs.indexOf("@");
							String tempMsg="";
							if(index!=-1){
								if(index==0){
									tempMsg="审核成功";
									retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
								}else{
									tempMsg=msgs.substring(0,index);
									retMsgs+="单据【"+msgs.substring(index+1)+"】 成功提示："+tempMsg+"<br/>";
								}
							}
						}
					}
				}catch (SQLException se) {
					String sbmsgs= se.getMessage();
					String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
					if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
						String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
						//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
						String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
						callStmt = conn.prepareCall(errSql);  
						callStmt.registerOutParameter(1, Types.VARCHAR);  
						callStmt.setString(2,exproc);
						callStmt.setString(3,buParams.getDsid());
						callStmt.execute();
						msgs= callStmt.getString(1);
					}else if(se.getErrorCode()== 20001){
						msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
						int index=msgs.indexOf("@");
						String tempMsg="";
						if(index!=-1){
							if(index==0){
								tempMsg="审核失败";
								retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
							}else{
								tempMsg=msgs.substring(0,index);
								retMsgs+="单据【"+msgs.substring(index+1)+"】 <span style='color:red'>失败提示： </span>"+tempMsg+"<br/>";
							}
						}else {
							tempMsg="审核失败";
							retMsgs+="单据【"+djIds[i]+"】 "+msgs.substring(index+1)+"<br/>";
						}
					}else{
                        retMsgs=semsgs;
						break;
					}
					cwReturnObject.setMsg("error");
				}catch (Exception e) {
					e.printStackTrace();
					cwReturnObject.setMsg("error");
					retMsgs=e.getMessage();
					//returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
				}finally{
					if(callStmt != null){
						callStmt.close();
					}
					if(conn != null){
						conn.close();
					}
				}
			}
		}else{
			throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid().split(";")[0]+"]的审核的存储过程");
		}
		retMsgs="<div style='line-height:23px;'>"+retMsgs+"</div>";
		cwReturnObject.setInfo(retMsgs);
		return cwReturnObject;
	}
	
	
	/***
	 * 财务：预付单和借款的通用付款
	 */
	@Override
	public cwReturnObject execCwPlFUKUAN(ButScripParams buParams) throws Exception {
		cwReturnObject cwReturnObject = new cwReturnObject();
		String retMsgs ="";
		String [] djIds=buParams.getDjId().split(";");
		CallableStatement callStmt = null;
		Connection conn = null;
		
		String dsId=buParams.getDsid().split(";").length>0?buParams.getDsid().split(";")[0]:buParams.getDsid();
		String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
		List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql).setString(0,dsId).setString(1,buParams.getPrefix()+"_fkfkd").list();
		if (commonP != null && !commonP.isEmpty()) {
			for (int i = 0; i < djIds.length; i++) {
				String msgs="";
				try {
					conn = this.getJdbcTemplate().getDataSource().getConnection();
					Object callObj = commonP.get(0).getProcedure();
					String callName = "";
					if (callObj != null || "".equals(callObj.toString())) {
						callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
						//执行存储过程(?,?,?,?)   pkid dsId  uid staid restr
						callStmt = conn.prepareCall(callName);
						callStmt.setString(1, djIds[i]);
						callStmt.setString(2, buParams.getDsid());
						callStmt.setString(3, buParams.getUid());
						callStmt.setString(4, buParams.getStaid());
						callStmt.registerOutParameter(5,OracleTypes.VARCHAR);
						callStmt.executeUpdate();
						cwReturnObject.setMsg("success");
						msgs=callStmt.getString(5);
						if(msgs==null||msgs.indexOf("@")==-1){
							System.out.println("单据DsId为："+dsId+"的审核的存储过程的V_OUT的输出不正确，没有传递djCode");
						}else{
							int index=msgs.indexOf("@");
							String tempMsg="";
							if(index!=-1){
								if(index==0){
									tempMsg="审核成功";
									retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
								}else{
									tempMsg=msgs.substring(0,index);
									retMsgs+="单据【"+msgs.substring(index+1)+"】 成功提示："+tempMsg+"<br/>";
								}
							}
						}
					}
				}catch (SQLException se) {
					String sbmsgs= se.getMessage();
					String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
					if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
						String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
						//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
						String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
						callStmt = conn.prepareCall(errSql);  
						callStmt.registerOutParameter(1, Types.VARCHAR);  
						callStmt.setString(2,exproc);
						callStmt.setString(3,buParams.getDsid());
						callStmt.execute();
						msgs= callStmt.getString(1);
					}else if(se.getErrorCode()== 20001){
						msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
						int index=msgs.indexOf("@");
						String tempMsg="";
						if(index!=-1){
							if(index==0){
								tempMsg="审核失败";
								retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
							}else{
								tempMsg=msgs.substring(0,index);
								retMsgs+="单据【"+msgs.substring(index+1)+"】 <span style='color:red'>失败提示： </span>"+tempMsg+"<br/>";
							}
						}else {
							tempMsg="审核失败";
							retMsgs+="单据【"+djIds[i]+"】 "+msgs.substring(index+1)+"<br/>";
						}
					}else{
                        retMsgs=semsgs;
						break;
					}
					cwReturnObject.setMsg("error");
				}catch (Exception e) {
					e.printStackTrace();
					cwReturnObject.setMsg("error");
					retMsgs=e.getMessage();
					//returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
				}finally{
					if(callStmt != null){
						callStmt.close();
					}
					if(conn != null){
						conn.close();
					}
				}
			}
		}else{
			throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid().split(";")[0]+"]的审核的存储过程");
		}
		retMsgs="<div style='line-height:23px;'>"+retMsgs+"</div>";
		cwReturnObject.setInfo(retMsgs);
		return cwReturnObject;
	}
	
	/***
	 *批量出库
	 */
	@Override
	public ReturnObject execButnProPLOutStock(ButScripParams buParams)
	throws Exception {
		ReturnObject returnObject = new ReturnObject();
		System.out.println(buParams.getDjId()+"--------------");
		String retMsgs ="";
		String [] djIds=buParams.getDjId().split(";");
		CallableStatement callStmt = null;
		Connection conn = null;
		try {
			String dsId=buParams.getDsid().split(";").length>0?buParams.getDsid().split(";")[0]:buParams.getDsid();
			String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
			List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql).setString(0,dsId).setString(1,buParams.getPrefix()+"_chuku").list();
			if (commonP != null && !commonP.isEmpty()) {
				for (int i = 0; i < djIds.length; i++) {
					String msgs="";
					try {
						conn = this.getJdbcTemplate().getDataSource().getConnection();
						Object callObj = commonP.get(0).getProcedure();
						String callName = "";
						if (callObj != null || "".equals(callObj.toString())) {
							callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
							//执行存储过程(?,?,?,?)   pkid dsId  uid staid restr
							callStmt = conn.prepareCall(callName);
							callStmt.setString(1,djIds[i]);
							callStmt.setString(2, buParams.getDsid());
							callStmt.setString(3, buParams.getUid());
							callStmt.setString(4, buParams.getStaid());
							callStmt.registerOutParameter(5,OracleTypes.VARCHAR);
							callStmt.executeUpdate();
							returnObject.setMsg("success");
							msgs=callStmt.getString(5);
							if(msgs==null||msgs.indexOf("@")==-1){
								System.out.println("单据DsId为："+dsId+"的审核的存储过程的V_OUT的输出不正确，没有传递djCode");
							}else{
								int index=msgs.indexOf("@");
								String tempMsg="";
								if(index!=-1){
									if(index==0){
										tempMsg="出库成功";
										retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
									}else{
										tempMsg=msgs.substring(0,index);
										retMsgs+="单据【"+msgs.substring(index+1)+"】 成功提示："+tempMsg+"<br/>";
									}
								}
							}
						}
					}catch (SQLException se) {
						String sbmsgs= se.getMessage();
						String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
						if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
							String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
							//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
							String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
							callStmt = conn.prepareCall(errSql);  
							callStmt.registerOutParameter(1, Types.VARCHAR);  
							callStmt.setString(2,exproc);
							callStmt.setString(3,buParams.getDsid());
							callStmt.execute();
							msgs= callStmt.getString(1);
						}else if(se.getErrorCode()== 20001){
							msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
							int index=msgs.indexOf("@");
							String tempMsg="";
							if(index!=-1){
								if(index==0){
									tempMsg="出库失败";
									retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
								}else{
									tempMsg=msgs.substring(0,index);
									retMsgs+="单据【"+msgs.substring(index+1)+"】 <span style='color:red'>失败提示： </span>"+tempMsg+"<br/>";
								}

							}
						}else{
							retMsgs=sbmsgs;
							break;
						}
						returnObject.setMsg("error");
					}catch (Exception e) {
						e.printStackTrace();
						returnObject.setMsg("error");
						msgs=e.getMessage();
						//returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
					}finally{
						if(callStmt != null){
							callStmt.close();
						}
						if(conn != null){
							conn.close();
						}
					}
				}
			}else{
				throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid().split(";")[0]+"]的出库的存储过程");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (retMsgs==null||"".equals(retMsgs)) {
			System.out.println(retMsgs);
			System.out.println("11111111111111111111");
		}
		retMsgs="<div style='line-height:23px;'>"+retMsgs+"</div>";
		returnObject.setInfo(retMsgs);

		return returnObject;
	}


	/***
	 * 批量入库
	 */
	@Override
	public ReturnObject execButnProPLInStock(ButScripParams buParams)
	throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String retMsgs ="";
		String [] djIds=buParams.getDjId().split(";");
		CallableStatement callStmt = null;
		Connection conn = null;
		try {
			String dsId=buParams.getDsid().split(";").length>0?buParams.getDsid().split(";")[0]:buParams.getDsid();
			String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
			List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql).setString(0,dsId).setString(1,buParams.getPrefix()+"_ruku").list();
			if (commonP != null && !commonP.isEmpty()) {
				for (int i = 0; i < djIds.length; i++) {
					String msgs="";
					try {
						conn = this.getJdbcTemplate().getDataSource().getConnection();
						Object callObj = commonP.get(0).getProcedure();
						String callName = "";
						if (callObj != null || "".equals(callObj.toString())) {
							callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
							//执行存储过程(?,?,?,?)   pkid dsId  uid staid restr
							callStmt = conn.prepareCall(callName);
							callStmt.setString(1,djIds[i]);
							callStmt.setString(2, buParams.getDsid());
							callStmt.setString(3, buParams.getUid());
							callStmt.setString(4, buParams.getStaid());
							callStmt.registerOutParameter(5,OracleTypes.VARCHAR);
							callStmt.executeUpdate();
							returnObject.setMsg("success");
							msgs=callStmt.getString(5);
							if(msgs==null||msgs.indexOf("@")==-1){
								System.out.println("单据DsId为："+dsId+"的审核的存储过程的V_OUT的输出不正确，没有传递djCode");
							}else{
								int index=msgs.indexOf("@");
								String tempMsg="";
								if(index!=-1){
									if(index==0){
										tempMsg="入库成功";
										retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
									}else{
										tempMsg=msgs.substring(0,index);
										retMsgs+="单据【"+msgs.substring(index+1)+"】 成功提示："+tempMsg+"<br/>";
									}
								}
							}
						}
					}catch (SQLException se) {
						String sbmsgs= se.getMessage();
						String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
						if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
							String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
							//执行存储过程，传入dsId和(exproc违反唯一约束的列名)
							String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
							callStmt = conn.prepareCall(errSql);  
							callStmt.registerOutParameter(1, Types.VARCHAR);  
							callStmt.setString(2,exproc);
							callStmt.setString(3,buParams.getDsid());
							callStmt.execute();
							msgs= callStmt.getString(1);
						}else if(se.getErrorCode()== 20001){
							msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
							int index=msgs.indexOf("@");
							String tempMsg="";
							if(index!=-1){
								if(index==0){
									tempMsg="入库失败";
									retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
								}else{
									tempMsg=msgs.substring(0,index);
									retMsgs+="单据【"+msgs.substring(index+1)+"】 <span style='color:red'>失败提示： </span>"+tempMsg+"<br/>";
								}

							}
						}else{
							retMsgs=sbmsgs;
							break;
						}
						returnObject.setMsg("error");
					}catch (Exception e) {
						e.printStackTrace();
						returnObject.setMsg("error");
						msgs=e.getMessage();
						//returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
					}finally{
						if(callStmt != null){
							callStmt.close();
						}
						if(conn != null){
							conn.close();
						}
					}
				}
			}else{
				throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid().split(";")[0]+"]的入库的存储过程");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		retMsgs="<div style='line-height:23px;'>"+retMsgs+"</div>";
		returnObject.setInfo(retMsgs);
		return returnObject;
	}





	@Override
	public ReturnObject zhuanHZ(ButScripParams buParams, String uid)
	throws Exception {
		ReturnObject returnObject = new ReturnObject();
		if (buParams.getDsid() == null || buParams.getDsid().equals("")) {
			returnObject.setMsg("没有传入单据的DSID，请检查");
			return returnObject;
		}
		//先将 buParams dsid - uid - rowId 
		String dbParams = "SELECT * FROM SYS_CW_BUTN_PROCEDURES WHERE DS_ID = '"+buParams.getDsid()+"'";
		Connection conn = null;
		PreparedStatement pstmt = null;
		CallableStatement callStmt = null; 
		try {
			//将数据保存到缓存表
			List<Map<String, Object>> tmpParamsL = this.getJdbcTemplate().queryForList(dbParams);
			if (tmpParamsL != null && tmpParamsL.size()==1 
					&& tmpParamsL.get(0).get("SAVE_SQL")!=null
					&& !"".equals(tmpParamsL.get(0).get("SAVE_SQL"))) {
				//执行校验的存储过程
				String saveSql = (String) tmpParamsL.get(0).get("SAVE_SQL");
				//判断编码是否配置
				String code = (String)(tmpParamsL.get(0).get("CODE") == null ? "" : tmpParamsL.get(0).get("CODE"));
				if ("".equals(code)) {
					returnObject.setMsg("执行校验的编码[CODE]没有配置");
					return returnObject;
				}

				//获取 参数的个数
				int pcount = 0;
				int index=-1;
				while((index=saveSql.indexOf('?',index))>-1){
					++index;
					++pcount;
				}
				conn = this.getJdbcTemplate().getDataSource().getConnection();
				//insert into tmp (A,B,C) values (?,?,?)  // dsid row  uid 
				for (int i = 0; i < buParams.getIds().size(); i++) {
					pstmt = conn.prepareStatement(saveSql);
					for (int j = 1; j < pcount+1; j++) {
						if (j == 1) {
							pstmt.setString(j, code);//code  jh  ..
						}else if (j == 2) {
							pstmt.setString(j,buParams.getIds().get(i));//后面的i是Map的String
						}else if(j == 3){
							pstmt.setString(j, uid);
						}
					}
					pstmt.execute();
				}
				//执行校验的存储过程
				String checkProc = (String)(tmpParamsL.get(0).get("CHECK_PROC") == null ? "" : tmpParamsL.get(0).get("CHECK_PROC"));
				String callName = "";
				//获取 参数的个数
				pcount = 0;
				index=-1;
				while((index=checkProc.indexOf('?',index))>-1){
					++index;
					++pcount;
				}
				if (!"".equals(checkProc)) {
					callName = "{call "+checkProc+"}";
					callStmt = conn.prepareCall(callName);
					//执行存储过程(?)   code  uid 
					for (int j = 1; j < pcount+1; j++) {
						if (j == 1) {
							callStmt.setString(1, code);//code  jh  ..
						}else if(j == 2){
							callStmt.setString(2, uid);
						}
					}
					callStmt.executeUpdate();
				}

				//执行转换的存储过程
				String execProc = (String)(tmpParamsL.get(0).get("EXECU_PROC") == null ? "" :tmpParamsL.get(0).get("EXECU_PROC") ) ;
				//获取 参数的个数
				pcount = 0;
				index=-1;
				while((index=execProc.indexOf('?',index))>-1){
					++index;
					++pcount;
				}
				if (!"".equals(execProc)) {
					callName = "{call "+execProc+"}";
					callStmt = conn.prepareCall(callName);
					//执行存储过程(?)   code
					//执行存储过程(?)   code  uid 
					for (int j = 1; j < pcount+1; j++) {
						if (j == 1) {
							callStmt.setString(1, code);//code  jh  ..
						}else if(j == 2){
							callStmt.setString(2, uid);
						}
					}
					callStmt.executeUpdate();
				}

				returnObject.setMsg("success");
			}else {
				returnObject.setMsg("没有在数据库中配置按钮的参数,请到表[SYS_CW_BUTN_PROCEDURES]中配置[dsid="+buParams.getDsid()+"]");
				return returnObject;
			}
		} catch (Exception e) {
			returnObject.setMsg(e.getMessage());
			e.printStackTrace();
		}finally{
			if (callStmt != null) {
				callStmt.close();
			}
			if (pstmt!= null) {
				pstmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return returnObject;
	}


    /***
     * 财务：应付费用单的(通用的批量审核的执行)
     */
    @Override
    public cwReturnObject cwCSSPlVerifyButn(ButScripParams buParams) throws Exception {
        cwReturnObject cwReturnObject = new cwReturnObject();
        String retMsgs ="";
        String [] djIds=buParams.getDjId().split(";");
        CallableStatement callStmt = null;
        Connection conn = null;

        String dsId=buParams.getDsid().split(";").length>0?buParams.getDsid().split(";")[0]:buParams.getDsid();
        String hql = " from CommonProcedure cp where cp.dsId = ? and cp.code = ?";
        List<CommonProcedure> commonP = this.getSessionFactory().getCurrentSession().createQuery(hql).setString(0,dsId).setString(1,buParams.getPrefix()+"_VERIFY_BUT").list();
        if (commonP != null && !commonP.isEmpty()) {
            for (int i = 0; i < djIds.length; i++) {
                String msgs="";
                try {
                    conn = this.getJdbcTemplate().getDataSource().getConnection();
                    Object callObj = commonP.get(0).getProcedure();
                    String callName = "";
                    if (callObj != null || "".equals(callObj.toString())) {
                        callName = "{call "+callObj.toString()+"(?,?,?,?,?)}";
                        //执行存储过程(?,?,?,?)   pkid dsId  uid staid restr
                        callStmt = conn.prepareCall(callName);
                        callStmt.setString(1,djIds[i]);
                        callStmt.setString(2, buParams.getDsid());
                        callStmt.setString(3, buParams.getUid());
                        callStmt.setString(4, buParams.getStaid());
                        callStmt.registerOutParameter(5,OracleTypes.VARCHAR);
                        callStmt.executeUpdate();
                        cwReturnObject.setMsg("success");
                        msgs=callStmt.getString(5);
                        if(msgs==null||msgs.indexOf("@")==-1){
                            System.out.println("单据DsId为："+dsId+"的审核的存储过程的V_OUT的输出不正确，没有传递djCode");
                        }else{
                            int index=msgs.indexOf("@");
                            String tempMsg="";
                            if(index!=-1){
                                if(index==0){
                                    tempMsg="审核成功";
                                    retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
                                }else{
                                    tempMsg=msgs.substring(0,index);
                                    retMsgs+="单据【"+msgs.substring(index+1)+"】 成功提示："+tempMsg+"<br/>";
                                }
                            }
                        }
                    }
                }catch (SQLException se) {
                    String sbmsgs= se.getMessage();
                    String semsgs=sbmsgs.substring(sbmsgs.indexOf(":")+1);
                    if(se.getErrorCode()== 1){ //违反唯一约束，还要获取字段，传入存储过程
                        String exproc= semsgs.substring(semsgs.indexOf(".")+1,(semsgs.indexOf(")")));//获取违反唯一约束的列名
                        //执行存储过程，传入dsId和(exproc违反唯一约束的列名)
                        String errSql="{?= call PK_SYS_COM.uf_GetIndexCol(?,?)}";
                        callStmt = conn.prepareCall(errSql);
                        callStmt.registerOutParameter(1, Types.VARCHAR);
                        callStmt.setString(2,exproc);
                        callStmt.setString(3,buParams.getDsid());
                        callStmt.execute();
                        msgs= callStmt.getString(1);
                    }else if(se.getErrorCode()== 20001){
                        msgs=se.getMessage().substring(se.getMessage().indexOf("ORA-20001:")+10,se.getMessage().indexOf("ORA-06512"));
                        int index=msgs.indexOf("@");
                        String tempMsg="";
                        if(index!=-1){
                            if(index==0){
                                tempMsg="审核失败";
                                retMsgs+="单据【"+msgs.substring(index+1)+"】 "+tempMsg+"<br/>";
                            }else{
                                tempMsg=msgs.substring(0,index);
                                retMsgs+="单据【"+msgs.substring(index+1)+"】 <span style='color:red'>失败提示： </span>"+tempMsg+"<br/>";
                            }
                        }else {
                            tempMsg="审核失败";
                            retMsgs+="单据【"+djIds[i]+"】 "+msgs.substring(index+1)+"<br/>";
                        }
                    }else{
                        retMsgs=semsgs;
                        break;
                    }
                    cwReturnObject.setMsg("error");
                }catch (Exception e) {
                    e.printStackTrace();
                    cwReturnObject.setMsg("error");
                    retMsgs=e.getMessage();
                    //returnObject.setInfo(returnObject.getInfo()+"<br/>"+e.getMessage());
                }finally{
                    if(callStmt != null){
                        callStmt.close();
                    }
                    if(conn != null){
                        conn.close();
                    }
                }
            }
        }else{
            throw new RuntimeException("没有找到对应的按钮存储过程，请配置<br/>[dsid:"+buParams.getDsid().split(";")[0]+"]的审核的存储过程");
        }
        retMsgs="<div style='line-height:23px;'>"+retMsgs+"</div>";
        cwReturnObject.setInfo(retMsgs);
        return cwReturnObject;
    }

}
